package cn.com.wms.Base;

import java.util.Date;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Set;

import org.hibernate.Query;


/**
 * 
 * @author zbzhu
 * 创建日期 2011-7-6
 * 类名      CommonOperate.java
 * 说明   拼装查询条件的工具类
 */
public class CommonOperate {
	
	/**
	 * 组织orderby语句
	 * @param order 
	 * @return orderby语句
	 */
	public static String getOrderby(LinkedHashMap<String,String> order){
		StringBuffer orderby = new StringBuffer("");
		if(order != null && order.size()>0){
			Set<String> keys = order.keySet();
			orderby.append(" order by ");
			for(String key: keys){
				orderby.append("o.").append(key).append(" ").append(order.get(key)).append(",");
			}
			orderby.deleteCharAt(orderby.length()-1);
		}
		return orderby.toString();
	}
	
	/**
	 * 获取过滤条件信息，有待于完善，还需要根据值的类型组织不同的SQL
	 * @param filters
	 * @return
	 */
	public static String getFilterStr(FilterInfo filters,boolean isSelect){
		StringBuffer filterStr = new StringBuffer("");
		if(filters !=null && filters.getAllFilterItems().size()>0){
			for(FilterItem filterItem: filters.getAllFilterItems()){
				String filterName = filterItem.getFilterName();
				int operation = filterItem.getOperation();
				Object filterValue = filterItem.getFilterValue();
				String alias = filterName;
				if(filterName.indexOf('.')!=-1){
					alias = filterName.replace('.', '_');
				}
				if(filterValue instanceof Date && (operation==LogicOperate.GT || operation==LogicOperate.GE)){
					alias = alias+"S";
				}
				else if(filterValue instanceof Date && (operation==LogicOperate.LT || operation==LogicOperate.LE)){
					alias = alias+"E";
				}
				
				if(filterItem.getAlias() != null && !"".equals(filterItem.getAlias())){
					switch(operation){				
						case LogicOperate.LIKE:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" like ?").append(" and ");
							break;
						case LogicOperate.UNLIKE:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" not like ?").append(" and ");
							break;
						case LogicOperate.GT:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" > ?").append(" and ");
							break;
						case LogicOperate.LT:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" < ?").append(" and ");
							break;
						case LogicOperate.GE:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" >= ?").append(" and ");
							break;
						case LogicOperate.LE:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" <= ?").append(" and ");
							break;
						case LogicOperate.EQUAL:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" = ?").append(" and ");
							break;
						case LogicOperate.UNEQUAL:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" != ?").append(" and ");
							break;
						case LogicOperate.ISNULL:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" is null and ");
							break;
						case LogicOperate.ISNOTNULL:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" is not null and ");
							break;
						case LogicOperate.IN:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" in (").append(filterValue).append(") and ");
							break;
						case LogicOperate.NOTIN:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" not in (").append(filterValue).append(") and ");
							break;
				
					}
				}else{
					switch(operation){				
						case LogicOperate.LIKE:
							filterStr.append(filterName).append(" like ?").append(" and ");
							break;
						case LogicOperate.UNLIKE:
							filterStr.append(filterName).append(" not like ?").append(" and ");
							break;
						case LogicOperate.GT:
							filterStr.append(filterName).append(" > ?").append(" and ");
							break;
						case LogicOperate.LT:
							filterStr.append(filterName).append(" < ?").append(" and ");
							break;
						case LogicOperate.GE:
							filterStr.append(filterName).append(" >= ?").append(" and ");
							break;
						case LogicOperate.LE:
							filterStr.append(filterName).append(" <= ?").append(" and ");
							break;
						case LogicOperate.EQUAL:
							filterStr.append(filterName).append(" = ?").append(" and ");
							break;
						case LogicOperate.UNEQUAL:
							filterStr.append(filterName).append(" != ?").append(" and ");
							break;
						case LogicOperate.ISNULL:
							filterStr.append(filterName).append(" is null and ");
							break;
						case LogicOperate.ISNOTNULL:
							filterStr.append(filterName).append(" is not null and ");
							break;
						case LogicOperate.IN:
							filterStr.append(filterName).append(" in (").append(filterValue).append(") and ");
							break;
						case LogicOperate.NOTIN:
							filterStr.append(filterName).append(" not in (").append(filterValue).append(") and ");
							break;
				
					}
				}
				
			}
			filterStr.delete(filterStr.lastIndexOf("and"), filterStr.length()-1);
		}
		
		if(isSelect){
			if(filters !=null && filters.getAllGroups().size()>0){
				filterStr.append(" group by ");
				for(Group group: filters.getAllGroups()){
					if(group.getAlias() != null && !"".equals(group.getAlias())){
						filterStr.append(group.getAlias()+".").append(group.getGroupName()).append(",");
					}else{
						filterStr.append(group.getGroupName()).append(",");
					}
				}
				filterStr.delete(filterStr.lastIndexOf(","), filterStr.length());
			}
			
			if(filters !=null && filters.getAllSorters().size()>0){
				filterStr.append(" order by ");
				for(Sorter sorter: filters.getAllSorters()){
					if(sorter.getAlias() != null && !"".equals(sorter.getAlias())){
						filterStr.append(sorter.getAlias()+".").append(sorter.getSortName()).append(" ").append(sorter.getSortOrder()).append(",");
					}else{
						filterStr.append(sorter.getSortName()).append(" ").append(sorter.getSortOrder()).append(",");
					}
				}
				filterStr.delete(filterStr.lastIndexOf(","), filterStr.length());
			}
		}
		
		
		return filterStr.toString();
	}
	
	
	/**20160311 chj 修改in notin
	 * 
	 * 
	 * 获取过滤条件信息，有待于完善，还需要根据值的类型组织不同的SQL,此方法用于hibernate中的session
	 * @param filters
	 * @return
	 */
	//此处需要增加判断，别名为空的情况,增加orderby情况，增加groupby的情况。
	public static String getFilterStr_H(FilterInfo filters,boolean isSelect){
		StringBuffer filterStr = new StringBuffer("");
		if(filters !=null && filters.getAllFilterItems().size()>0){
			for(FilterItem filterItem: filters.getAllFilterItems()){
				String filterName = filterItem.getFilterName();
				int operation = filterItem.getOperation();
				Object filterValue = filterItem.getFilterValue();
				String alias = filterName;
				if(filterName.indexOf('.')!=-1){
					alias = filterName.replace('.', '_');
				}
				if(filterValue instanceof Date && (operation==LogicOperate.GT || operation==LogicOperate.GE)){
					alias = alias+"S";
				}
				else if(filterValue instanceof Date && (operation==LogicOperate.LT || operation==LogicOperate.LE)){
					alias = alias+"E";
				}
				if(filterItem.getAlias() != null && !"".equals(filterItem.getAlias())){
					switch(operation){
						case LogicOperate.LIKE:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" like :").append(alias).append(" and ");
							break;
						case LogicOperate.UNLIKE:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" not like :").append(alias).append(" and ");
							break;
						case LogicOperate.GT:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" > :").append(alias).append(" and ");
							break;
						case LogicOperate.LT:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" < :").append(alias).append(" and ");
							break;
						case LogicOperate.GE:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" >= :").append(alias).append(" and ");
							break;
						case LogicOperate.LE:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" <= :").append(alias).append(" and ");
							break;
						case LogicOperate.EQUAL:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" = :").append(alias).append(" and ");
							break;
						case LogicOperate.UNEQUAL:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" != :").append(alias).append(" and ");
							break;
						case LogicOperate.ISNULL:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" is null and ");
							break;
						case LogicOperate.ISNOTNULL:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" is not null and ");
							break;
						case LogicOperate.IN:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" in (").append(filterValue).append(") and ");
							break;
						case LogicOperate.NOTIN:
							filterStr.append(filterItem.getAlias()+".").append(filterName).append(" not in (").append(filterValue).append(") and ");
							break;
				
					}
				}else{
					switch(operation){
						case LogicOperate.LIKE:
							filterStr.append(filterName).append(" like :").append(alias).append(" and ");
							break;
						case LogicOperate.UNLIKE:
							filterStr.append(filterName).append(" not like :").append(alias).append(" and ");
							break;
						case LogicOperate.GT:
							filterStr.append(filterName).append(" > :").append(alias).append(" and ");
							break;
						case LogicOperate.LT:
							filterStr.append(filterName).append(" < :").append(alias).append(" and ");
							break;
						case LogicOperate.GE:
							filterStr.append(filterName).append(" >= :").append(alias).append(" and ");
							break;
						case LogicOperate.LE:
							filterStr.append(filterName).append(" <= :").append(alias).append(" and ");
							break;
						case LogicOperate.EQUAL:
							filterStr.append(filterName).append(" = :").append(alias).append(" and ");
							break;
						case LogicOperate.UNEQUAL:
							filterStr.append(filterName).append(" != :").append(alias).append(" and ");
							break;
						case LogicOperate.ISNULL:
							filterStr.append(filterName).append(" is null and ");
							break;
						case LogicOperate.ISNOTNULL:
							filterStr.append(filterName).append(" is not null and ");
							break;
						case LogicOperate.IN:
							filterStr.append(filterName).append(" in (").append(filterValue).append(") and ");
							break;
						case LogicOperate.NOTIN:
							filterStr.append(filterName).append(" not in (").append(filterValue).append(") and ");
							break;
				
					}
				}
				
			}
			filterStr.delete(filterStr.lastIndexOf("and"), filterStr.length()-1);
		}
		
		if(isSelect){
			if(filters !=null && filters.getAllGroups().size()>0){
				filterStr.append(" group by ");
				for(Group group: filters.getAllGroups()){
					if(group.getAlias() != null && !"".equals(group.getAlias())){
						filterStr.append(group.getAlias()+".").append(group.getGroupName()).append(",");
					}else{
						filterStr.append(group.getGroupName()).append(",");
					}
				}
				
				filterStr.delete(filterStr.lastIndexOf(","), filterStr.length());
			}
			
			if(filters !=null && filters.getAllSorters().size()>0){
				filterStr.append(" order by ");
				for(Sorter sorter: filters.getAllSorters()){
					if(sorter.getAlias() != null && !"".equals(sorter.getAlias())){
						filterStr.append(sorter.getAlias()+".").append(sorter.getSortName()).append(" ").append(sorter.getSortOrder()).append(",");
					}else{
						filterStr.append(sorter.getSortName()).append(" ").append(sorter.getSortOrder()).append(",");
					}
				}
				filterStr.delete(filterStr.lastIndexOf(","), filterStr.length());
			}
		}
		
		
		return filterStr.toString();
	}
	
	/** 20160311 chj 修改 过滤in notin条件赋值，这两个赋值直接在之前过滤条件中赋值
	*@param filters
	*@return
	*@author chj 
	*@date 2016-3-11 下午12:03:16 
	*@comment 
	*/ 
	public static Map fillQueryValue(FilterInfo filters){
		Map query = new LinkedHashMap();
		if(filters !=null && filters.getAllFilterItems().size()>0){
			for(FilterItem filterItem: filters.getAllFilterItems()){
				String filterName = filterItem.getFilterName();
				Object filterValue = filterItem.getFilterValue();
				int operation = filterItem.getOperation();
				if(operation != LogicOperate.ISNULL && operation != LogicOperate.ISNOTNULL){
					String alias = filterName;
					if(filterName.indexOf('.')!=-1){
						alias = filterName.replace('.', '_');
					}
					if(filterValue instanceof Date && (operation==LogicOperate.GT || operation==LogicOperate.GE)){
						alias = alias+"S";
						Date newValue = (Date)filterValue;
						newValue.setHours(0);
						newValue.setMinutes(0);
						newValue.setSeconds(0);
						filterValue = newValue;
					}
					else if(filterValue instanceof Date && (operation==LogicOperate.LT || operation==LogicOperate.LE)){
						alias = alias+"E";
						Date newValue = (Date)filterValue;
						newValue.setHours(23);
						newValue.setMinutes(59);
						newValue.setSeconds(59);
						filterValue = newValue;
					}else if(filterItem.getOperation() == LogicOperate.LIKE){
						filterValue = "%"+filterValue+"%";
					}else if(filterItem.getOperation() == LogicOperate.IN||filterItem.getOperation() == LogicOperate.NOTIN){
						continue;
					}
					
					query.put(alias, filterValue);
				}
			}
		}
		return query;
	}
	
	/**
	 * 填充查询的值
	 * @param filters
	 * @param query
	 * @return
	 */
	public static Query fillQueryValue(FilterInfo filters,Query query){
		if(filters !=null && filters.getAllFilterItems().size()>0){
			for(FilterItem filterItem: filters.getAllFilterItems()){
				String filterName = filterItem.getFilterName();
				Object filterValue = filterItem.getFilterValue();
				int operation = filterItem.getOperation();
				if(operation != LogicOperate.ISNULL && operation != LogicOperate.ISNOTNULL){
					String alias = filterName;
					if(filterName.indexOf('.')!=-1){
						alias = filterName.replace('.', '_');
					}
					if(filterValue instanceof Date && (operation==LogicOperate.GT || operation==LogicOperate.GE)){
						alias = alias+"S";
						Date newValue = (Date)filterValue;
						newValue.setHours(0);
						newValue.setMinutes(0);
						newValue.setSeconds(0);
						filterValue = newValue;
					}
					else if(filterValue instanceof Date && (operation==LogicOperate.LT || operation==LogicOperate.LE)){
						alias = alias+"E";
						Date newValue = (Date)filterValue;
						newValue.setHours(23);
						newValue.setMinutes(59);
						newValue.setSeconds(59);
						filterValue = newValue;
					}else if(filterItem.getOperation() == LogicOperate.LIKE){
						filterValue = "%"+filterValue+"%";
					}else if(filterItem.getOperation() == LogicOperate.IN||filterItem.getOperation() == LogicOperate.NOTIN){
						continue;
					}
					query.setParameter(alias, filterValue);
				}
			}
		}
		return query;
	}
	
	
	public static Object[] fillQueryValueArr(FilterInfo filters){
		Map map = fillQueryValue(filters);
		Object[] obj = null;
		int i = 0;
		if(!map.isEmpty()){
			int length = map.keySet().size();
			obj = new Object[length];
			for(Object o : map.keySet()){
				obj[i] = map.get(o);
				i++;
			}
		}
		return obj;
	}
	
}
